
package com.huizhan.web.dao;
 
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;
import com.huizhan.web.model.*;
import java.util.*;


@Repository 
public interface ExhibitorDao extends JpaRepository<Exhibitor,Long>
{
    /**
     * 根据项目ID获取门禁点集合
     * @param projectId 项目类型ID
     * @return 项目门禁点集合
     */
    List<Exhibitor> findByProjectId(String projectId);

    /**
     * 分页查询展商集合
     * @param name 姓名
     * @param phone 手机号
     * @param pageable 分页参数
     * @param project_Id 项目ID
     * @param code 展位
     * @return 观众集合
     */
    @Query(
        value = "SELECT * FROM exhibitor"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3  = '') "
            + " AND (booth LIKE CONCAT('%',?4,'%') OR ?4  = '' )"
            + " AND (UNIX_TIMESTAMP(create_date) >= ?5 OR ?5 = 0)"
            + " AND (UNIX_TIMESTAMP(create_date) <= ?6 OR ?6 = 0)",
        countQuery = "SELECT COUNT(1) FROM exhibitor"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '' )"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3  = '') "
            + " AND (booth LIKE CONCAT('%',?4,'%') OR ?4  = '' )"
            + " AND (UNIX_TIMESTAMP(create_date) >= ?5 OR ?5 = 0)"
            + " AND (UNIX_TIMESTAMP(create_date) <= ?6 OR ?6 = 0)",
        nativeQuery = true
    )
    Page<Exhibitor> findByWhere(String name,String phone,String projectId,String booth, Long startDate,Long endDate,Pageable  pageable);
    
    /**
     * 分页查询展商集合
     * @param name 姓名
     * @param phone 手机号
     * @param pageable 分页参数
     * @param project_Id 项目ID
     * @param code 展位
     * @return 观众集合
     */
    @Query(
        value = "SELECT * FROM exhibitor"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3  = '') "
            + " AND (booth LIKE CONCAT('%',?4,'%') OR ?4  = '' )"
            + " AND (state = ?5 OR ?5 = 0)",
        countQuery = "SELECT COUNT(1) FROM exhibitor"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '' )"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3  = '') "
            + " AND (booth LIKE CONCAT('%',?4,'%') OR ?4  = '' )"
            + " AND (state = ?5 OR ?5 = 0)",
        nativeQuery = true
    )
    Page<Exhibitor> findByWhere(String name,String phone,String projectId,String booth,Integer state, Pageable  pageable);

    /**
     * 
     * @param projectId
     * @param phone
     * @return
     */
    Exhibitor findByProjectIdAndPhone(String projectId,String phone);
}
